package org.vserver.dao;

import org.apache.log4j.Logger;
import org.jboss.seam.annotations.Name;
import org.jboss.seam.annotations.Scope;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.vserver.entity.User;
import org.vserver.util.CryptoUtil;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import static org.jboss.seam.ScopeType.SESSION;

/**
 * User: karpden ( Denis Karpov )
 * Date: 3/16/11
 * Time: 6:48 PM
 */


@Scope(SESSION)
@Name("UserDao")
public class UserDao implements Serializable{
    private final Logger log = Logger.getLogger(UserDao.class);

    static final String SEL_BASE =
            "SELECT\n" +
            "users.id,\n" +
            "users.code,\n" +
            "users.lastname,\n" +
            "users.firstname,\n" +
            "users.birthday,\n" +
            "users.sex,\n" +
            "users.email\n" +
            "FROM users";

    static final String SEL_ID =
            "SELECT\n" +
            "users.id,\n" +
            "users.code,\n" +
            "users.lastname,\n" +
            "users.firstname,\n" +
            "users.birthday,\n" +
            "users.sex,\n" +
            "users.email\n" +
            "FROM users\n" +
            "WHERE users.id= ?";

    static final String SEL_CODE =
            "SELECT\n" +
            "users.id,\n" +
            "users.code,\n" +
            "users.lastname,\n" +
            "users.firstname,\n" +
            "users.birthday,\n" +
            "users.sex,\n" +
            "users.email\n" +
            "FROM users\n" +
            "WHERE users.code= ?";

    static final String SEL_PWD =
            "SELECT\n" +
            "users.password\n" +
            "FROM users\n" +
            "WHERE users.id= ?";

    static final String SET_PWD =
            "UPDATE users\n" +
            "set users.password = ?\n" +
            "WHERE users.id= ?";

    static final String UPDATE =
            "UPDATE users\n" +
            "SET\n" +
            "code = :code,\n" +
            "lastname = :lastname,\n" +
            "firstname = :firstname,\n" +
            "birthday = :birthday\n" +
            "sex = :sex,\n" +
            "email = :email\n" +
            "WHERE id = :id";
    static final String SEL_ROLES4USER =
            "SELECT r.id, r.code " +
                    "FROM roles r, user_roles ur " +
                    "WHERE ur.user_id = ? AND ur.role_id = r.id";

    static final String DEL_ID = "DELETE FROM users WHERE id=?";

    private String jndiName;
    private SimpleJdbcInsert insertTemplate;
    private SimpleJdbcTemplate jdbcTemplate;
    private final UserRowMapper rowMapper = new UserRowMapper();
    private User currentUser;
    private DataSource dataSource;

    public UserDao() {
    }

    public String getJndiName() {
        return jndiName;
    }

    public void setJndiName(String jndiName) {
        this.jndiName = jndiName;
        try {
            Context iContext = new InitialContext();
            DataSource ds = null;
            try {
                if (ds==null) ds = (DataSource) iContext.lookup(jndiName);
            } catch (NamingException e) {
                log.error(e);
            }

            log.info("QQQQQQQQQ "+ds);

            setDataSource(ds);
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }
//    @PostConstruct
//    public void init(){
//        try {
//            Context iContext = new InitialContext();
//            DataSource ds = null;
//            try {
//                if (ds==null) ds = (DataSource) iContext.lookup("java:comp/env/jdbc/mysql");
////                if (ds==null) ds = (DataSource) iContext.lookup("java:comp/env/jdbc/TestDB");
//            } catch (NamingException e) {
//                System.out.println("QQ_5");
//                System.out.println(e);
////                e.printStackTrace();
//            }
//
//            log.info("QQQQQQQQQ "+ds);
//
//            setDataSource(ds);
//        } catch (NamingException e) {
//            e.printStackTrace();
//        }
//    }

    public User getCurrentUser() {
        return currentUser;
    }
    public void setCurrentUser(User currentUser) {
        this.currentUser = currentUser;
    }

    public void setDataSource(DataSource dataSource) {
        insertTemplate = new SimpleJdbcInsert(dataSource).withTableName("users");
        jdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.dataSource = dataSource;
        //this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public String getTestCon(){
        String res="";
        try {
            Connection conn = dataSource.getConnection();
            log.info("Database connection established");
            PreparedStatement st = conn.prepareStatement("select * from users");
            try {
                st.execute();
                ResultSet rs = st.getResultSet();
                StringBuilder sb = new StringBuilder();
                while (rs.next()) {
                    sb.append("===============================\n");
                    for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                        sb.append(rs.getString(i)).append("\t");
                    }
                    sb.append("\n");
                }
                res = sb.toString();
                log.info("RES="+res);
            } finally {
                st.close();
            }
        } catch (SQLException e) {
            log.error(e);
        }
        return res;
    }

    public User getUser(Long id){
        User u = jdbcTemplate.queryForObject(SEL_ID, rowMapper, id);
        if (u!=null){
            u.setRoles(getUserRoles(u));
        }
        return u;
    }

    public User loadUserByCode(User user){
        return jdbcTemplate.queryForObject(SEL_CODE, new UserRowMapperUpdateer(user), user.getCode());
    }
    public User getUser(String code){
        return jdbcTemplate.queryForObject(SEL_CODE,rowMapper,code);
    }

    public boolean checkPwd(User user, String pwd){
        final String pwdHash = jdbcTemplate.queryForObject(SEL_PWD,String.class,user.getId());
        return CryptoUtil.checkPwd(pwd, pwdHash);
    }

    public List<User> allUsers(){
        return jdbcTemplate.query(SEL_BASE,rowMapper);
    }

// Insert
    public void insert(User user){
        SqlParameterSource parameters = new BeanPropertySqlParameterSource(user);
        Number newId = insertTemplate.executeAndReturnKey(parameters);
        user.setId(newId.longValue());
    }

// Update
    public void setPassword(User user, String pwd){
        jdbcTemplate.update(SET_PWD,pwd,user.getId());
    }
    public void update(User user){
        SqlParameterSource parameters = new BeanPropertySqlParameterSource(user);
        jdbcTemplate.update(UPDATE, parameters);
    }
    public void delete(User user){
        jdbcTemplate.update(DEL_ID, user.getId());
    }

    public List<String> getUserRoles(User user) {
        return jdbcTemplate.query(SEL_ROLES4USER,
                new RowMapper<String>(){
                    @Override
                    public String mapRow(ResultSet resultSet, int i) throws SQLException {
                        return resultSet.getString("code");
                    }
                },user.getId() );
    }

    class UserRowMapper implements RowMapper<User>, Serializable{
        @Override
        public User mapRow(ResultSet resultSet, int i) throws SQLException {
            User user = new User();
            user.setId(resultSet.getLong("id"));
            user.setCode(resultSet.getString("code"));
            user.setLastname(resultSet.getString("lastname"));
            user.setFirstname(resultSet.getString("firstname"));
            user.setEmail(resultSet.getString("email"));
            user.setBirthday(resultSet.getDate("birthday"));
            user.setSex(resultSet.getString("sex"));
            return user;
        }
    }

    class UserRowMapperUpdateer implements RowMapper<User>, Serializable{
        private User user;

        UserRowMapperUpdateer(User user) {
            this.user = user;
        }

        @Override
        public User mapRow(ResultSet resultSet, int i) throws SQLException {
            user.setId(resultSet.getLong("id"));
            user.setCode(resultSet.getString("code"));
            user.setLastname(resultSet.getString("lastname"));
            user.setFirstname(resultSet.getString("firstname"));
            user.setEmail(resultSet.getString("email"));
            user.setBirthday(resultSet.getDate("birthday"));
            user.setSex(resultSet.getString("sex"));
            return user;
        }
    }
}
